# coding=utf-8


import requests
from bs4 import BeautifulSoup
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

import pandas as pd
import time, datetime
from .models import phase_data
from django.db.models import Max

pd.set_option('display.max_rows', None)

cn = create_engine('sqlite:///django_lottory.sqlite3')  # 便于同步github，先放d:盘
# 按期数查
start_phase = '2007001'
end_phase = time.strftime('%Y', time.localtime(time.time())) + '200'  # 一年最大146或145期，按年份加200基本可以
# https://chart.cp.360.cn/kaijiang/slt/?lotId=120029&chartType=undefined&spanType=3&span=2007001_2019028&r=0.7504661856439394#roll_0'


_START_DATE = '2007-05-01'  # 大乐透从2007年5月才开始的
_NOWTIME = time.strftime('%Y-%m-%d', time.localtime(time.time()))  # 默认系统当前日期


def _url_date(starttime=_START_DATE):  # 根据日期创建查url
    # url = 'https://chart.cp.360.cn/kaijiang/slt/?lotId=120029&chartType=undefined&spanType=2&span=' + starttime + '_' + _NOWTIME + '&r=0.2729831739188433#roll_132'
    url = 'https://chart.cp.360.cn/kaijiang/slt/?lotId=120029&chartType=undefined&spanType=2&span=' + starttime + '_' + _NOWTIME
    return url


def _url_phase(startphase=start_phase, endphase=end_phase):  # 根据期数创建url
    # url = 'https://chart.cp.360.cn/kaijiang/slt/?lotId=120029&chartType=undefined&spanType=3&span=' + startphase + '_' + endphase + '&r=0.7504661856439394#roll_0'
    url = 'https://chart.cp.360.cn/kaijiang/slt/?lotId=120029&chartType=undefined&spanType=3&span=' + startphase + '_' + endphase
    return url


def _get_html(urls):
    response = requests.get(urls)
    if response.status_code == 200:  # 有数据则返回的为200
        return response.text
    else:
        print('无数据！')
    return None


def _to_sqlite(html):  # 将数据存入sqlite数据库
    if html == 'phasenum':
        html = _get_html(_url_phase(_getMaxPhaseFromTable(),end_phase))
    else:
        html = _get_html(_url_date(str(_getMaxdateFromTable())))
    bf = BeautifulSoup(html, "html.parser")
    s = bf.find_all("tr")  # 返回的是tr列表

    a = []
    for u in range(4, len(s)):  # 从第4行开始，因为前三行为标题
        i = s[u].contents
        s1 = i[2].text.split('\xa0')
        s2 = i[3].text.split('\xa0')
        df = phase_data(phasenum=i[0].text, date=i[1].text[0:10], red1=s1[0], red2=s1[1], red3=s1[2],
                        red4=s1[3],
                        red5=s1[4], blue1=s2[0], blue2=s2[1], jackpot=i[4].text, firstnum=i[5].text,
                        firstprice=i[6].text, secondnum=i[7].text,secondprice=i[8].text,totalbet=i[9].text)
        a.append(df)
    phase_data.objects.bulk_create(a)
    return '共更新' + str(len(s)) + '期数据'


# 获取表中最大日期,否则返回默认_STARTTIME
def _getMaxdateFromTable():
    result = phase_data.objects.all()
    result = result.aggregate(Max('date'))  # 返回一个字典

    if result['date__max'] is None:  # date__max是aggregate函数自动添加的后缀，注意是两个下划线。如果用count则是date__count
        maxdate = _START_DATE
    else:
        maxdate = result['date__max']
    return _date_add1day(maxdate)


# 获取表中最大期数,否则返回默认_期数
def _getMaxPhaseFromTable():
    result = phase_data.objects.all()
    result = result.aggregate(Max('phasenum'))  # 返回一个字典

    if result['phasenum__max'] is None:  # __max是aggregate函数自动添加的后缀，注意是两个下划线。如果用count则是date__count
        maxphase = start_phase
    else:
        maxphase = result['phasenum__max']
        maxphase = str(int(maxphase) + 1)
    return maxphase


def _date_add1day(maxdate):
    a = datetime.datetime.strptime(maxdate, '%Y-%m-%d')
    a = a + datetime.timedelta(days=1)
    return (a.date())

# _to_sqlite(_get_html(_url_date(str(_getMaxdateFromTable('dlt')))))
